package by.sjc.giz.dao.repositiory.impl;

import by.sjc.giz.dao.base.AbstractHibernateDao;
import by.sjc.giz.dao.entity.SubjectEntity;
import by.sjc.giz.dao.entity.shop.BookInShopEntity;
import by.sjc.giz.dao.repositiory.ShopDao;
import by.sjc.giz.model.BooksFilter;
import by.sjc.giz.model.SubjectType;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by irina on 10.08.14.
 */
@Repository
@Transactional
public class ShopRepository extends AbstractHibernateDao<SubjectEntity, Long> implements ShopDao {

    public static final Logger logger = Logger.getLogger(ShopRepository.class);

    @SuppressWarnings("unchecked")
    private List<BookInShopEntity> getBooksByIdList(List<Integer> idList) {
        if (idList == null || idList.size() == 0)
            return new ArrayList<BookInShopEntity>(1);

        return getSession().createCriteria(BookInShopEntity.class)
                .add( Restrictions.in("id", idList))
                .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
                .list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BookInShopEntity> getBookList(int shopId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "from BookInShopEntity where shop_id=:shopId";
        return getSession().createQuery(hql)
                .setParameter("shopId", shopId)
                .list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SubjectEntity> getShopList() {
        logger.debug("shop list ++++++++++");
        Criteria criteria = getSession().createCriteria(SubjectEntity.class).add(Restrictions.eq("type", SubjectType.SHOP));
        return criteria.list();
    }

    @Override
    public BookInShopEntity getBookInShopById(int shopId, int bookId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "from BookInShopEntity where shop_id=:shopId and book_id=:bookId";
        return (BookInShopEntity) getSession().createQuery(hql)
                .setParameter("shopId", shopId)
                .setParameter("bookId",bookId)
                .uniqueResult();
    }

    @Override
    public int getBookCount(int shopId, int bookId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "select shop_book.count from BookInShopEntity shop_book where shop_id=:shopId and book_id=:bookId";
        return (Integer) getSession().createQuery(hql)
                .setParameter("shopId", shopId)
                .setParameter("bookId",bookId)
                .uniqueResult();
    }


    @Override
    public void updateBookInShop(BookInShopEntity book) {
        getSession().update(book);
    }

    @Override
    public List<String> getProfitableGenres(int shopId) {

        String sql = "select profit.name " +
                "from (SELECT ge.id,ge.name, sum(ob.count) as count, " +
                "sum(ob.count*ob.book_price) as sum " +
                "FROM genres ge " +
                "join books_genres bg on bg.genre_id=ge.id " +
                "join books bo on bo.id=bg.book_id " +
                "join order_books ob on ob.book_id=bo.id " +
                "join orders ord on ord.id=ob.order_id " +
                "where recipient_id=:shopId GROUP BY ge.id) as profit " +
                "join books_genres bg on bg.genre_id=profit.id " +
                "join books bo on bo.id=bg.book_id " +
                "join order_books ob on ob.book_id=bo.id " +
                "join orders ord on ord.id=ob.order_id " +
                "where sender_id=:shopId group by profit.id " +
                "ORDER BY (profit.sum/profit.count - " +
                "sum(ob.book_price*ob.count)/sum(ob.count))*profit.count DESC";

        return getSession().createSQLQuery(sql)
                .setParameter("shopId", shopId)
                .setMaxResults(5)
                .list();
    }

    @Override
    public void addBookToStore(BookInShopEntity book) {
        BookInShopEntity bookInShopEntity = getBookInShopById(book.getShop().getId(), book.getBookEntity().getId());
        if (bookInShopEntity == null) {
            getSession().save("BookInShopEntity", book);
        }
        else {
            bookInShopEntity.setCount(bookInShopEntity.getCount() + book.getCount());
            float price = book.getSellPrice() > bookInShopEntity.getSellPrice()
                    ? book.getSellPrice()
                    : bookInShopEntity.getSellPrice();
            bookInShopEntity.setSellPrice(price);
            updateBookInShop(bookInShopEntity);
        }
    }

    private Criteria createBaseCriteria(BooksFilter filter) {
        Criteria criteria = getSession().createCriteria(BookInShopEntity.class)
                .createAlias("bookEntity", "book");

        if (filter.getGenres() != null) {
            criteria.createCriteria("book.genreEntitySet","genre")
                    .add( Restrictions.in("genre.name", filter.getGenres()) );
        }
        if (filter.getAuthors() != null) {
            criteria.createCriteria("book.authorEntitySet","author")
                    .add( Restrictions.in("author.name", filter.getAuthors()) );
        }
        if (filter.getShops() != null) {
            criteria.createCriteria("this.shop","shop")
                    .add( Restrictions.in("shop.name", filter.getShops()) )
                    .add( Restrictions.eq("shop.type", SubjectType.SHOP) );
        }
        if (filter.getPublishers() != null) {
            criteria.createCriteria("book.publisher","publisher")
                    .add( Restrictions.in("publisher.name", filter.getPublishers()) )
                    .add( Restrictions.eq("publisher.type", SubjectType.PUBLISHER) );
        }
        if (filter.getMinPrice() != null) {
            criteria.add( Restrictions.ge( "sellPrice", filter.getMinPrice()) );
        }
        if (filter.getMaxPrice() != null) {
            criteria.add( Restrictions.le("sellPrice", filter.getMaxPrice()) );
        }
        if (filter.getName() != null) {
            criteria.add( Restrictions.ilike("book.name",filter.getName(), MatchMode.ANYWHERE) );
        }

        return criteria;
    }


    @Override
    public List<BookInShopEntity> getBooks(int pageSize, int pageNumber, BooksFilter filter) {
        Criteria criteria = createBaseCriteria(filter)
                .setProjection(Projections.groupProperty("this.id"));

        if (pageSize > 0) {
            criteria.setMaxResults(pageSize)
                    .setFirstResult(pageSize * (pageNumber - 1));
        }

        List<Integer> idList = criteria.list();
        logger.info("BookInShopEntity id list =" + idList);
        return getBooksByIdList(idList);
    }

    @Override
    public int getBooksCount(BooksFilter filter) {
        Criteria criteria = createBaseCriteria(filter)
                .setProjection(Projections.countDistinct("this.id"));
        return ((Long) criteria.uniqueResult()).intValue();
    }

    @Override
    public float getBookPrice(int shopId, int bookId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "select sellPrice from BookInShopEntity where shop_id=:shopId and book_id=:bookId";
        Float res = (Float) getSession().createQuery(hql)
                .setParameter("shopId", shopId)
                .setParameter("bookId", bookId)
                .uniqueResult();
        return res.floatValue();
    }

    @Override
    public float getProfit(int shopId, Date startDate, Date endDate) {
        String sqlIncome = "select sum(ob.count*ob.book_price) from order_books ob " +
                "join orders ord on ob.order_id=ord.id " +
                "where ord.recipient_id=:recipientId and ord.state='satisfied' "+
                "and (ord.change_date between :startDate and :endDate)";

        String sqlOutgo = "select sum(ob.count*ob.book_price) from order_books ob " +
                "join orders ord on ob.order_id=ord.id " +
                "where ord.sender_id=:senderId and ord.state='satisfied' " +
                "and (ord.change_date between :startDate and :endDate)";

        Object in = getSession().createSQLQuery(sqlIncome)
                .setParameter("recipientId", shopId)
                .setParameter("startDate", startDate)
                .setParameter("endDate", endDate)
                .uniqueResult();
        Object out = getSession().createSQLQuery(sqlOutgo)
                .setParameter("senderId", shopId)
                .setParameter("startDate", startDate)
                .setParameter("endDate", endDate)
                .uniqueResult();
        float income = in  == null ? 0 : Float.valueOf(in.toString());
        float outgo  = out == null ? 0 : Float.valueOf(out.toString());

        return  income-outgo;
    }

    @Override
    public Date getDateOfFirstOrder(int shopId) {
        String sql = "select MIN(ord.change_date) from orders ord " +
                "where ( ord.recipient_id=:shopId or ord.sender_id=:shopId ) " +
                "and ord.state='satisfied'";

        return (Date)getSession().createSQLQuery(sql)
                .setParameter("shopId", shopId)
                .uniqueResult();
    }

    @Override
    public Date getDateOfLastOrder(int shopId) {
        String sql = "select MAX(ord.change_date) from orders ord " +
                "where ( ord.recipient_id=:shopId or ord.sender_id=:shopId ) " +
                "and ord.state='satisfied'";

        return (Date)getSession().createSQLQuery(sql)
                .setParameter("shopId", shopId)
                .uniqueResult();
    }
}
